package com.oocl.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.oocl.dao.CustomerDao;
import com.oocl.pojo.Customer;
import com.oocl.util.DBUtil;

public class CustomerDaoImpl implements CustomerDao{

	@Override
	public int addCustomer(Customer customer) {
		Connection connection  = null;
		connection = DBUtil.getConnection();
		PreparedStatement ps = null;
		String sql = "insert into customer(id,cname,salary,birth_date,sex) values(seq_c1.nextval,?,?,?,?)";
		int n=0;
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, customer.getCname());
			ps.setDouble(2, customer.getSalary());
			if(null==customer.getBirthDate()){
				ps.setDate(3, null);
			}else {
				ps.setDate(3, new Date(customer.getBirthDate().getTime()));
			}
			ps.setBoolean(4, customer.isSex());
			n = ps.executeUpdate();
			int cid = this.findCustomerIdByName(customer.getCname());
			if(null!=customer.getHobby() && customer.getHobby().length>0){
				this.addHobby(cid, customer.getHobby());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return n;
	}
	
	@Override
	public int addHobby(Integer cid,String[] hobby){
		String sql = "insert into cus_hobby(id,c_id,h_id) values(seq_c2.nextval,?,?)";
		PreparedStatement ps = null;
		Connection connection  = null;
		connection = DBUtil.getConnection();
		int m=0;
		try {
			ps = connection.prepareStatement(sql);
			for(int i=0;i<hobby.length;i++){
				int hid = Integer.parseInt(hobby[i]);
				ps.setInt(1, cid);
				ps.setInt(2, hid);
				ps.addBatch();
			}
			int[] ms = ps.executeBatch();
			m = ms.length;
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return m;
	}

	@Override
	public int deleteCustomerById(Integer id) {
		Connection connection  = null;
		connection = DBUtil.getConnection();
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		String sql1 = "delete from customer where id=?";
		int n=0;
		try {
			this.deleteHobbyByCid(id);
			ps1 = connection.prepareStatement(sql1);
			ps1.setInt(1, id);
			n = ps1.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return n;
	}
	
	@Override
	public int deleteHobbyByCid(Integer cid) {
		Connection connection  = null;
		connection = DBUtil.getConnection();
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		String sql = "delete from cus_hobby where c_id=?";
		int n=0;
		try {
			ps = connection.prepareStatement(sql);
			ps.setInt(1, cid);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return n;
	}

	@Override
	public int updateCustomer(Customer customer) {
		Connection connection  = null;
		connection = DBUtil.getConnection();
		PreparedStatement ps = null;
		String sql = "update customer set cname=?,salary=?,birth_date=?,sex=? where id=?";
		int n=0;
		try {
			this.deleteHobbyByCid(customer.getId());
			if(null!=customer.getHobby() && customer.getHobby().length>0){
				this.addHobby(customer.getId(), customer.getHobby());
			}
			
			ps = connection.prepareStatement(sql);
			ps.setString(1, customer.getCname());
			ps.setDouble(2, customer.getSalary());
			ps.setDate(3, new Date(customer.getBirthDate().getTime()));
			ps.setBoolean(4, customer.isSex());
			ps.setInt(5, customer.getId());
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return n;
	}

	@Override
	public List<Customer> findAllCustomers() {
		Connection connection  = null;
		connection = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from customer";
		List<Customer> customers = new ArrayList<Customer>();
		Customer customer = null;
		try {
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				customer = new Customer();
				customer.setId(rs.getInt(1));
				customer.setCname(rs.getString(2));
				customer.setSalary(rs.getDouble(3));
				customer.setBirthDate(new java.util.Date(rs.getDate(4).getTime()));
				customer.setSex(rs.getBoolean(5));
				customers.add(customer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customers;
	}
	
	@Override
	public List<String> findHobbyByCid(Integer cid) {
		Connection connection  = null;
		connection = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select ch.c_id,h.hname from cus_hobby ch,hobby h where ch.h_id=h.id and c_id=?";
		List<String> hobbyList = new ArrayList<>();
		try {
			ps = connection.prepareStatement(sql);
			ps.setInt(1, cid);
			rs = ps.executeQuery();
			while(rs.next()){
				hobbyList.add(rs.getString(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return hobbyList;
	}

	@Override
	public int findCustomerIdByName(String cname) {
		Connection connection  = null;
		connection = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select id from customer where cname=?";
		int id = -1;
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, cname);
			rs = ps.executeQuery();
			while(rs.next()){
				id = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}

	@Override
	public Customer findCustomerById(Integer id) {
		Connection connection  = null;
		connection = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from customer where id=?";
		Customer customer = null;
		try {
			ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				customer = new Customer();
				customer.setId(rs.getInt(1));
				customer.setCname(rs.getString(2));
				customer.setSalary(rs.getDouble(3));
				customer.setBirthDate(new java.util.Date(rs.getDate(4).getTime()));
				customer.setSex(rs.getBoolean(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customer;
	}
	
}
